﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VK.Casemeet;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Employees
{
    public static class RoleDAL
    {
        internal static RoleEntity GetRoleByReader(IDataReader dr)
        {
            RoleEntity role = null;

            role = new RoleEntity();
            role.RoleID = dr.GetInt32(0);
            role.Name = dr.IsDBNull(1) ? null : dr.GetString(1);
            role.ProvinceID = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            role.CityID = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
            role.ProvinceName = dr.IsDBNull(4) ? "全部地区" : dr.GetString(4);
            role.CityName = dr.IsDBNull(5) ? null : dr.GetString(5);

            return role;
        }

        internal static int GetRoleCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectRole = @"SELECT COUNT([Role].[RoleID]) FROM [Role] LEFT OUTER JOIN
                          Area AS CityArea ON Role.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Role.ProvinceID = ProvinceArea.AreaID ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectRole, ref parameterlst);
            DbCommand SelectRoleCommand = db.GetSqlStringCommand(SelectRole);
            DbHelper.PackDbCommandByDbParameters(SelectRoleCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectRoleCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static IList<RoleEntity> GetRoleList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<RoleEntity> Rolelst = new List<RoleEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectRole = @"
                SELECT [RoleID],[Name],[ProvinceID],[CityID],[ProvinceName],[CityName]
	            FROM (
                    SELECT [Role].[RoleID],[Role].[Name],[Role].[ProvinceID],[Role].[CityID],
                        ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName
                        ,ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectRole += " [" + sortField + "] " + sortMode + ",";
            SelectRole += @" [RoleID] Desc
                ) AS rowNum 
                    FROM [Role] LEFT OUTER JOIN
                          Area AS CityArea ON Role.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Role.ProvinceID = ProvinceArea.AreaID";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectRole, ref parameterlst);
            SelectRole += @"
                ) AS [Role] 
	            WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectRoleCommand = db.GetSqlStringCommand(SelectRole);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectRoleCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectRoleCommand))
            {
                while (dr.Read())
                {
                    RoleEntity Role = GetRoleByReader(dr);

                    Rolelst.Add(Role);
                }
            }

            return Rolelst;
        }

        internal static IList<RoleEntity> GetRoleList()
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<RoleEntity> Rolelst = new List<RoleEntity>();

            string SelectRole = @"SELECT [Role].[RoleID],[Role].[Name],[Role].[ProvinceID],[Role].[CityID],
                    ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName
                FROM [Role] LEFT OUTER JOIN
                    Area AS CityArea ON Role.CityID = CityArea.AreaID LEFT OUTER JOIN
                    Area AS ProvinceArea ON Role.ProvinceID = ProvinceArea.AreaID ";
            DbCommand SelectRoleCommand = db.GetSqlStringCommand(SelectRole);

            using (IDataReader dr = db.ExecuteReader(SelectRoleCommand))
            {
                while (dr.Read())
                {
                    RoleEntity Role = GetRoleByReader(dr);

                    Rolelst.Add(Role);
                }
            }

            return Rolelst;
        }

        internal static RoleEntity GetRole(int roleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            RoleEntity role = null;

            string SelectRole = @"
                SELECT [Role].[RoleID],[Role].[Name],[Role].[ProvinceID],[Role].[CityID],
                    ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName
                FROM [Role] LEFT OUTER JOIN
                          Area AS CityArea ON Role.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Role.ProvinceID = ProvinceArea.AreaID
	           ";

            DbCommand SelectRoleCommand = db.GetSqlStringCommand(SelectRole);

            SelectRoleCommand.CommandText += " WHERE [Role].RoleID=@RoleID ";
            db.AddInParameter(SelectRoleCommand, "@RoleID", DbType.Int32, roleID);

            using (IDataReader dr = db.ExecuteReader(SelectRoleCommand))
            {
                if (dr.Read())
                {
                    role = GetRoleByReader(dr);
                    role.RoleRightslst = new List<RoleRightsEntity>();
                    string SelectRoleRights = @"SELECT [RoleID],[RightsID] FROM [RoleRights] WHERE [RoleID]=@RoleID";
                    DbCommand SelectRoleRightsCommand = db.GetSqlStringCommand(SelectRoleRights);
                    db.AddInParameter(SelectRoleRightsCommand, "@RoleID", DbType.Int32, roleID);
                    using (IDataReader drRoleRights = db.ExecuteReader(SelectRoleRightsCommand))
                    {
                        while (drRoleRights.Read())
                        {
                            RoleRightsEntity roleRights = new RoleRightsEntity();
                            roleRights.RoleID = drRoleRights.GetInt32(0);
                            roleRights.RightsID = drRoleRights.GetInt32(1);
                            role.RoleRightslst.Add(roleRights);
                        }
                    }
                }
            }

            return role;
        }

        internal static int InsertRole(RoleEntity role)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertRole = @"Declare @ID int;
            INSERT INTO [Role] ([Name],[ProvinceID],[CityID]) VALUES (@Name,@ProvinceID,@CityID); 
            SELECT @ID=@@IDENTITY;";
            foreach (RoleRightsEntity roleRights in role.RoleRightslst)
            {
                InsertRole += "INSERT INTO [RoleRights] ([RoleID],[RightsID]) VALUES (@ID," + roleRights.RightsID + ");";
            }

            DbCommand InsertRoleCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Transaction_InsertRole", InsertRole));
            db.AddInParameter(InsertRoleCommand, "@Name", DbType.String, role.Name);
            db.AddInParameter(InsertRoleCommand, "@ProvinceID", DbType.Int32, role.ProvinceID);
            db.AddInParameter(InsertRoleCommand, "@CityID", DbType.Int32, role.CityID);
            result = db.ExecuteNonQuery(InsertRoleCommand);

            return result;
        }

        internal static int UpdateRole(RoleEntity role)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateRole = @"
            UPDATE [Role]
            SET Name=@Name,[ProvinceID]=@ProvinceID,[CityID]=@CityID
            WHERE [RoleID] = @RoleID;
            DELETE FROM [RoleRights] WHERE [RoleID]=@RoleID;";

            foreach (RoleRightsEntity roleRights in role.RoleRightslst)
            {
                UpdateRole += "INSERT INTO [RoleRights] ([RoleID],[RightsID]) VALUES (@RoleID," + roleRights.RightsID + ");";
            }

            DbCommand UpdateRoleCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Transaction_UpdateRole", UpdateRole));
            db.AddInParameter(UpdateRoleCommand, "@Name", DbType.String, role.Name);
            db.AddInParameter(UpdateRoleCommand, "@RoleID", DbType.Int32, role.RoleID);
            db.AddInParameter(UpdateRoleCommand, "@ProvinceID", DbType.Int32, role.ProvinceID);
            db.AddInParameter(UpdateRoleCommand, "@CityID", DbType.Int32, role.CityID);
            result = db.ExecuteNonQuery(UpdateRoleCommand);

            return result;
        }

        internal static int DeleteRole(int roleID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteRole = @"DELETE FROM [RoleRights] WHERE RoleID=@RoleID;
            DELETE FROM [Role] WHERE RoleID=@RoleID;";

            DbCommand DeleteRoleCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Transaction_DeleteRole", DeleteRole));
            db.AddInParameter(DeleteRoleCommand, "@RoleID", DbType.Int32, roleID);
            result = db.ExecuteNonQuery(DeleteRoleCommand);

            return result;
        }

        internal static IList<RightsEntity> GetRightsList()
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<RightsEntity> rightslst = new List<RightsEntity>();

            string SelectRights = @"SELECT [RightsID],[Name],[Group] FROM [Rights] ORDER BY [Group]";
            DbCommand SelectRightsCommand = db.GetSqlStringCommand(SelectRights);

            using (IDataReader dr = db.ExecuteReader(SelectRightsCommand))
            {
                while (dr.Read())
                {
                    RightsEntity rights = new RightsEntity();
                    rights.RightsID = dr.GetInt32(0);
                    rights.Name = dr.IsDBNull(1) ? null : dr.GetString(1);
                    rights.Group = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
                    rightslst.Add(rights);
                }
            }

            return rightslst;
        }
    }
}
